import pymysql
import pandas as pd
import numpy as np

#想计算数据库各项值的每月差值
list_feature = ['company', 'consume', 'capital', 'house_price','employees','population']
num_inputs=len(list_feature)
feature_y="GDP"
distinct="data_pudong"
def readData(feature,Y, distinct):
    # 参数1:主机名或工P地址; 参数2:用户名; 参数3:密码; 参数4: 数据库名称
    # db = pymysql.connect(host="localhost", user="root",password="password", database="smart_cim")
    #db = pymysql.connect(host="localhost",port=3307, user="app_user",password="123456", database="smart_cim")
    result_min=np.array([10000.0,10000.0,10000.0,10000.0,10000.0,10000.0])
    max_index=[0,0,0,0,0,0]
    result_max=np.array([0.0,0.0,0.0,0.0,0.0,0.0])
    min_index=[0,0,0,0,0,0]
    result_sum=[0,0,0,0,0,0]
    result_mean=[0,0,0,0,0,0]
    db = pymysql.connect(host="localhost",port=3306, user="app_user",password="123456", database="smart_cim")
    sql = 'select * from ' + distinct
    dataframes = pd.read_sql(sql, db)
    #print(dataframes.describe())
    dataframe = dataframes[feature]
    dataY = dataframes[Y]
    datasets = dataframe.values
    dataY =dataY.values
    for i in range(len(datasets[0])):
        for j in  range(1,len(datasets)):
            temp=float(datasets[j][i])-float(datasets[j-1][i])
            result_sum[i]+=temp
            if temp>=result_max[i]:
                result_max[i]=temp
                max_index[i]=j
            if temp<result_min[i]:
                result_min[i]=temp
                min_index[i]=j
    for i in range(len(datasets[0])):
        result_mean[i]=result_sum[i]/(len(datasets))
    db.close()
    print(result_max)
    print(result_min)
    print(max_index)
    print(min_index)
    print(result_mean)
    return result_max ,result_min
readData(list_feature,feature_y,distinct)

# result_max
# [1.2062e+04 3.3000e+01 2.1830e+03 1.3030e+03 4.3400e+00 1.9800e+00]
#result_min 目测数据库里数据有些问题啊，都是0
# [0. 0. 0. 0. 0. 0.]
# index  坐标
# [150, 91, 10, 175, 26, 192]
# [161, 1, 78, 151, 128, 31]
#mean_result
#[1949.9264705882354, 4.842401960784314, 485.3480392156863, 248.11764705882354, 0.9473529411764707, 0.33230392156862754]

#测试numpy 的 enumerate
# a = np.array([[0.0, 0.0, 0.0], [1.0, 1.0, 1.0]])
# b = np.array([[1.2, 2.3, 3.4], [4.5, 5.6, 7.8], [9.10, 10.11, 11.12]])
# for i, aSample in enumerate(a):
#     for j, bSample in enumerate(b):
#         print(aSample)
#         print(bSample)
#         print(bSample-aSample)